﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using DapperExtensions;
using DapperExtensions.Mapper;
using iAgileFramework.DataTransCarrier;

namespace demo.DBHelper
{
    /// <summary>
    /// 说明：使用Dapper以及Repository方式的Dao
    /// 时间：2016-5-3 14:49:03
    /// </summary>
    /// <typeparam name="T">表对象</typeparam>
    public class RepositoryDao<T> : IRepositoryDao<T> where T : class
    {
        /// <summary>
        /// 当前会话
        /// </summary>
        private Session Session { get; set; }

        /// <summary>
        /// DAO层仓储构造
        /// </summary>
        public RepositoryDao()
        {
            this.Session = SessionManager.GetSession<T>();
        }

        #region 新增方法
        /// <summary>
        /// 插入单条记录
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <param name="entity">实体</param>
        /// <returns>主键值</returns>
        public dynamic Add(T entity)
        {
            dynamic result = this.Session.Connection.Insert<T>(entity, this.Session.Transaction);
            return result;
        }

        /// <summary>
        /// 批量插入功能
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <param name="entityList">实体迭代对象</param>
        /// <returns>是否成功</returns>
        public bool Add(IEnumerable<T> entityList)
        {
            this.Session.Connection.Insert(entityList, this.Session.Transaction);
            return true;
        }
        #endregion

        #region 修改方法
        /// <summary>
        /// 更新单条记录
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <param name="entity">实体</param>
        /// <returns>是否成功</returns>
        public bool Update(T entity)
        {
            return this.Session.Connection.Update(entity, this.Session.Transaction);
        }

        /// <summary>
        /// 批量修改功能
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <param name="entityList">实体迭代对象</param>
        /// <returns>是否成功</returns>
        public bool Update(IEnumerable<T> entityList)
        {
            #region 连接状态初始化
            bool isClosed = false;
            if (this.Session.Connection.State == ConnectionState.Closed)
            {
                this.Session.Connection.Open();
                isClosed = true;
            }
            #endregion

            #region 开始事务
            IDbTransaction transaction = null;
            bool sessionTran = false;
            if (this.Session.Transaction == null)
                transaction = this.Session.Connection.BeginTransaction();
            else
            {
                sessionTran = true;
                transaction = this.Session.Transaction;
            }
            #endregion

            #region 批量更新
            foreach (T entity in entityList)
            {
                if (!this.Session.Connection.Update(entity, transaction))
                {
                    if (!sessionTran) transaction.Rollback();
                    return false;
                }
            }
            #endregion

            //提交事务
            if (!sessionTran) transaction.Commit();

            //恢复关闭链接
            if (isClosed) this.Session.Connection.Close();

            return true;
        }

        /// <summary>
        /// 更新指定字段
        /// </summary>
        /// <param name="entity">实体</param>
        /// <param name="fields">字段名称</param>
        /// <returns>是否成功</returns>
        protected bool Update(IEnumerable<T> entityList, IEnumerable<string> fieldNames)
        {
            #region 构造有效更新字段
            var entityType = typeof(T);
            //匹配字段
            IList<string> propertyList = new List<string>();
            if (fieldNames != null && fieldNames.Count() > 0)
            {
                foreach (var p in entityType.GetProperties(BindingFlags.Public | BindingFlags.Instance))
                {
                    foreach (string n in fieldNames)
                    {
                        if (p.Name.ToLower() == n.ToLower())
                        {
                            propertyList.Add(n);
                        }
                    }
                }
            }

            IClassMapper classMap = DapperExtensions.DapperExtensions.GetMap<T>();
            var whereFields = classMap.Properties.Where(p => p.KeyType != KeyType.NotAKey);

            if (!whereFields.Any())
            {
                throw new ArgumentException(string.Format("表{0}没有有效的主键，请确认", entityType.Name));
            }
            #endregion

            #region 连接状态初始化
            bool isClosed = false;
            if (this.Session.Connection.State == ConnectionState.Closed)
            {
                this.Session.Connection.Open();
                isClosed = true;
            }
            #endregion

            #region 开始事务
            IDbTransaction transaction = null;
            bool sessionTran = false;
            if (this.Session.Transaction == null)
                transaction = this.Session.Connection.BeginTransaction();
            else
            {
                sessionTran = true;
                transaction = this.Session.Transaction;
            }
            #endregion

            #region 批量指定字段更新
            StringBuilder sql = new StringBuilder();

            foreach (T e in entityList)
            {
                sql.AppendFormat("UPDATE [{0}] SET", entityType.Name);

                foreach (var p in propertyList)
                {
                    sql.AppendFormat(" [{0}] = @{0} ,", p);
                }

                sql.Remove(sql.Length - 1, 1);
                sql.AppendLine(string.Format("WHERE [{0}] = @{0}", whereFields.First().Name));

                int i = this.Session.Connection.Execute(sql.ToString(), e, transaction, null, CommandType.Text);
                if (i == 0)
                {
                    if (!sessionTran) transaction.Rollback();
                    return false;
                }

                sql.Clear();
            }
            #endregion

            //提交事务
            if (!sessionTran) transaction.Commit();

            //恢复关闭链接
            if (isClosed) this.Session.Connection.Close();

            return true;
        }
        #endregion

        #region 删除方法
        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="entity">实体</param>
        /// <returns>是否成功</returns>
        public bool Delete(T entity)
        {
            return this.Session.Connection.Delete(entity, this.Session.Transaction);
        }

        /// <summary>
        /// 批量删除
        /// </summary>
        /// <param name="entityList">实体迭代对象</param>
        /// <returns>是否成功</returns>
        public bool Delete(IEnumerable<T> entityList)
        {
            #region 连接状态初始化
            bool isClosed = false;
            if (this.Session.Connection.State == ConnectionState.Closed)
            {
                this.Session.Connection.Open();
                isClosed = true;
            }
            #endregion

            #region 开始事务
            IDbTransaction transaction = null;
            bool sessionTran = false;
            if (this.Session.Transaction == null)
                transaction = this.Session.Connection.BeginTransaction();
            else
            {
                sessionTran = true;
                transaction = this.Session.Transaction;
            }
            #endregion

            #region 批量删除
            foreach (T entity in entityList)
            {
                if (!this.Session.Connection.Delete(entity, transaction))
                {
                    if (!sessionTran) transaction.Rollback();
                    return false;
                }
            }
            #endregion

            //提交事务
            if (!sessionTran) transaction.Commit();

            //恢复关闭链接
            if (isClosed) this.Session.Connection.Close();

            return true;
        }

        /// <summary>
        /// 说明：根据主键值删除
        /// 时间：2016-4-28 14:48:10
        /// </summary>
        /// <param name="key">主键值</param>
        /// <returns></returns>
        public bool Delete(dynamic key)
        {
            IClassMapper classMap = DapperExtensions.DapperExtensions.GetMap<T>();
            var whereFields = classMap.Properties.Where(p => p.KeyType != KeyType.NotAKey);

            if (!whereFields.Any())
            {
                throw new ArgumentException(string.Format("表{0}没有有效的主键，请确认", typeof(T).Name));
            }

            IPredicate predicate = new FieldPredicate<T>()
            {
                Not = false,
                Operator = Operator.Eq,
                PropertyName = whereFields.First().Name,
                Value = key
            };

            return this.Session.Connection.Delete<T>(predicate, this.Session.Transaction);
        }

        /// <summary>
        /// 说明：根据主键批量删除
        /// 时间：2016-4-28 15:04:18
        /// </summary>
        /// <param name="keys">主键集合</param>
        /// <returns>是否成功</returns>
        public bool Delete(IEnumerable<dynamic> keys)
        {
            IClassMapper classMap = DapperExtensions.DapperExtensions.GetMap<T>();
            var whereFields = classMap.Properties.Where(p => p.KeyType != KeyType.NotAKey);

            if (!whereFields.Any())
            {
                throw new ArgumentException(string.Format("表{0}没有有效的主键，请确认", typeof(T).Name));
            }

            PredicateGroup predicateGroup = new PredicateGroup();
            predicateGroup.Predicates = new List<IPredicate>();
            predicateGroup.Operator = GroupOperator.Or;

            foreach (object v in keys)
            {
                IPredicate predicate = new FieldPredicate<T>()
                {
                    Not = false,
                    Operator = Operator.Eq,
                    PropertyName = whereFields.First().Name,
                    Value = whereFields.First().PropertyInfo.GetValue(v, null)
                };
                predicateGroup.Predicates.Add(predicate);
            }

            return this.Session.Connection.Delete<T>(predicateGroup, this.Session.Transaction);
        }

        /// <summary>
        /// 说明：根据表达式删除数据
        /// 备注：此方法具有一定入侵性，所以设置为protected，避免在BLL层或是其他地方使用导致后期换DAL层困难
        /// 时间：2016-4-28 14:55:55
        /// </summary>
        /// <param name="predicate">Linq表达式</param>
        /// <returns>是否成功</returns>
        protected bool Delete(Expression<Func<T, object>> expression, IEnumerable<object> values)
        {
            PredicateGroup predicateGroup = new PredicateGroup();
            predicateGroup.Predicates = new List<IPredicate>();
            predicateGroup.Operator = GroupOperator.Or;

            foreach (object v in values)
            {
                var predicate = Predicates.Field<T>(expression, Operator.Eq, v);
                predicateGroup.Predicates.Add(predicate);
            }

            return this.Session.Connection.Delete<T>(predicateGroup, this.Session.Transaction);
        }
        #endregion

        #region 列表查询
        /// <summary>
        /// 说明：获得所有数据
        /// </summary>
        /// <returns>可迭代泛型对象</returns>
        public IEnumerable<T> GetAll()
        {
            bool buffered = true;
            var sql = string.Format("SELECT * FROM [{0}]", typeof(T).Name);
            return SqlMapper.Query<T>(this.Session.Connection, sql, null, this.Session.Transaction, buffered, null, CommandType.Text);
        }

        /// <summary>
        /// 根据SQL查询原表数据
        /// </summary>
        /// <param name="sql">查询SQL</param>
        /// <param name="param">参数</param>
        /// <param name="buffered">是否缓冲</param>
        /// <returns>可迭代泛型对象</returns>
        protected IEnumerable<T> GetList(string sql, object param = null, bool buffered = true)
        {
            return SqlMapper.Query<T>(this.Session.Connection, sql, param, this.Session.Transaction, buffered);
        }

        /// <summary>
        /// 根据SQL查询（适用模型视图）
        /// </summary>
        /// <param name="sql">查询SQL</param>
        /// <param name="param">参数</param>
        /// <param name="buffered">是否缓冲</param>
        /// <returns>可迭代泛型对象</returns>
        protected IEnumerable<TEntity> GetList<TEntity>(string sql, object param = null, bool buffered = true) where TEntity : class
        {
            return SqlMapper.Query<TEntity>(this.Session.Connection, sql, param, this.Session.Transaction, buffered).ToList<TEntity>();
        }

        /// <summary>
        /// 分页获取实体列表
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="search"></param>
        /// <param name="buffered"></param>
        /// <returns></returns>
        protected PageList<T> GetPageList(string sql, SearchBase search, bool buffered = true)
        {
            return this.GetPageList<T>(sql, search, buffered);
        }

        /// <summary>
        /// 分页获取实体列表
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <param name="search">查询对象</param>
        /// <param name="buffered">是否缓冲</param>
        /// <returns></returns>
        protected PageList<TEntity> GetPageList<TEntity>(string sql, SearchBase search, bool buffered = true)
        {
            string countSql = string.Format(@"SELECT COUNT(*) AS Total FROM ( {0} ) AS a", sql);
            int count = SqlMapper.Query<int>(this.Session.Connection, countSql, search).Single<int>();
            search.Pager.TotalItemCount = count;

            string listSql = string.Format(@"SELECT *
                                             FROM ( SELECT *, ROW_NUMBER() OVER(ORDER BY {0}) AS 'RowNumber'
                                                    FROM ( {1} ) as c) a
                                             WHERE a.RowNumber >= {2}
                                                 AND a.RowNumber <= {3}", string.Join(",", search.Pager.SortList), sql, search.Pager.StartRecordIndex, search.Pager.EndRecordIndex);

            IEnumerable<TEntity> entityList = this.Session.Connection.Query<TEntity>(listSql, search);

            PageList<TEntity> pagerList = new PageList<TEntity>()
            {
                TotalItemCount = count,
                Lists = entityList.ToList<TEntity>(),
                PageSize = search.Pager.PageSize,
                CurrentPageIndex = search.Pager.CurrentPageIndex
            };
            return pagerList;
        }
        #endregion

        #region 其他：单项查询、判断、执行
        /// <summary>
        /// 根据主键获得模型
        /// </summary>
        /// <param name="key">主键</param>
        /// <returns>表实体对象</returns>
        public T GetById(dynamic key)
        {
            return this.Session.Connection.Get<T>(key as object, this.Session.Transaction);
        }

        /// <summary>
        /// 获取单值
        /// </summary>
        /// <typeparam name="TScale">单值类型</typeparam>
        /// <param name="sql">语句</param>
        /// <param name="param">参数</param>
        /// <param name="buffered">是否缓冲</param>
        /// <returns></returns>
        protected TScale GetScale<TScale>(string sql, dynamic param = null, bool buffered = true)
        {
            IEnumerable<TScale> rt = SqlMapper.Query<TScale>(this.Session.Connection, sql, param, this.Session.Transaction, buffered);

            return rt.Single<TScale>();
        }

        /// <summary>
        /// 判断是否存在
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <param name="param">参数</param>
        /// <param name="buffered">是否缓冲</param>
        /// <returns>是否存在</returns>
        protected bool Exists(string sql, dynamic param = null, bool buffered = true)
        {
            string sqlExists = string.Format(@"IF(EXISTS({0}))
	                                                SELECT 1
                                                ELSE
	                                                SELECT 0", sql);
            int ret = this.GetScale<int>(sqlExists, param, buffered);

            return (ret == 1);
        }

        /// <summary>
        /// 执行sql操作
        /// </summary>
        /// <param name="sql">sql</param>
        /// <param name="param">匿名对象</param>
        /// <returns></returns>
        protected int Execute(string sql, object param = null)
        {
            return this.Session.Connection.Execute(sql, param, this.Session.Transaction);
        }

        #endregion

    }
}
